﻿using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;

namespace ConsolePro.Util
{
    public class SqlBulkHelper
    {
        /// <summary>
        /// 获取批量插入的SQL语句
        /// </summary>
        /// <typeparam name="T">Entity</typeparam>
        /// <param name="list">EntityList</param>
        /// <param name="tableName">表名，如果空，取EntityClassName的复数形式</param>
        /// <param name="keyName">主键字段名称(如果KeyName是空值，生成的SQL会包含Id值的插入)</param>
        /// <returns></returns>
        public (string sql, List<MySqlParameter> paramList) GetBulkInsertSql<T>(ICollection<T> list, string tableName = "", string keyName = "Id") where T : class
        {
            if (list == null || list.Count == 0) return (default, default);
            var type = typeof(T);
            var pros = type.GetProperties();
            if (string.IsNullOrWhiteSpace(tableName)) tableName = ToPlural(type.Name);
            var sb = new StringBuilder($"INSERT INTO `{tableName}` (");

            var placeholder = new StringBuilder("(");
            var count = 0;

            foreach (var v in pros)
            {
                // 私有类型不管
                if (!v.PropertyType.IsPublic) continue;
                // 取值类型与string
                if (v.PropertyType.IsValueType || v.PropertyType == typeof(string))
                {
                    if (v.Name == keyName) continue;
                    sb.Append($"`{v.Name}`,");
                    placeholder.Append($"@a{count++},");
                }
            }

            sb.Remove(sb.Length - 1, 1).Append(") Values ");

            var resultPlaceholder = placeholder.Remove(placeholder.Length - 1, 1).Append(")").ToString();
            count = 0;
            // SQL 占位符&参数 生成
            List<MySqlParameter> sqlParameters = new List<MySqlParameter>();
            foreach (var v in list)
            {
                // 添加MYSQL Param
                int i = 0;
                foreach (var p in pros)
                {
                    if (p.Name == keyName) continue;
                    sqlParameters.Add(new MySqlParameter($"@{count}a{i++}", p.GetValue(v)));
                }

                sb.Append(resultPlaceholder.Replace("a", count + "a") + ",");
                count++;
            }

            return (sb.Remove(sb.Length - 1, 1).Append(";").ToString(), sqlParameters);
        }

        readonly Regex plural1 = new Regex("(?<keep>[^aeiou])y$");
        readonly Regex plural2 = new Regex("(?<keep>[aeiou]y)$");
        readonly Regex plural3 = new Regex("(?<keep>[sxzh])$");
        readonly Regex plural4 = new Regex("(?<keep>[^sxzhy])$");

        /// <summary>
        /// 单词变成复数形式
        /// </summary>
        /// <param name="word"></param>
        /// <returns></returns>
        private string ToPlural(string word)
        {
            if (plural1.IsMatch(word))
                return plural1.Replace(word, "${keep}ies");
            else if (plural2.IsMatch(word))
                return plural2.Replace(word, "${keep}s");
            else if (plural3.IsMatch(word))
                return plural3.Replace(word, "${keep}es");
            else if (plural4.IsMatch(word))
                return plural4.Replace(word, "${keep}s");
            return word;
        }

    }
}
